
###### Get historical data on Karnataka ######
villages <- read.dta("data/Karnataka_GP_ReservationHistory.dta")


### First, let's rank GP's according to SC/ST proportion
x = villages %>%
  mutate(quota_1993 = case_when(
    reserved_bc_1993==0 & reserved_st_1993==0 & reserved_sc_1993==0 & reserved_w_1993==0 ~ "",
    reserved_bc_1993==0 & reserved_st_1993==1 & reserved_sc_1993==0 & reserved_w_1993==0 ~ "ST",
    reserved_bc_1993==0 & reserved_st_1993==0 & reserved_sc_1993==0 & reserved_w_1993==1 ~ "W",
    reserved_bc_1993==0 & reserved_st_1993==1 & reserved_sc_1993==0 & reserved_w_1993==1 ~ "ST + W")) %>%
  mutate(quota_2000 = case_when(
    reserved_bc_2000==0 & reserved_st_2000==0 & reserved_sc_2000==0 & reserved_w_2000==0 ~ "",
    reserved_bc_2000==0 & reserved_st_2000==1 & reserved_sc_2000==0 & reserved_w_2000==0 ~ "ST",
    reserved_bc_2000==0 & reserved_st_2000==0 & reserved_sc_2000==0 & reserved_w_2000==1 ~ "W",
    reserved_bc_2000==0 & reserved_st_2000==1 & reserved_sc_2000==0 & reserved_w_2000==1 ~ "ST + W")) %>%
  mutate(quota_2002 = case_when(
    reserved_bc_2002==0 & reserved_st_2002==0 & reserved_sc_2002==0 & reserved_w_2002==0 ~ "",
    reserved_bc_2002==0 & reserved_st_2002==1 & reserved_sc_2002==0 & reserved_w_2002==0 ~ "ST",
    reserved_bc_2002==0 & reserved_st_2002==0 & reserved_sc_2002==0 & reserved_w_2002==1 ~ "W",
    reserved_bc_2002==0 & reserved_st_2002==1 & reserved_sc_2002==0 & reserved_w_2002==1 ~ "ST + W")) %>%
  mutate(quota_2005 = case_when(
    reserved_bc_2005==0 & reserved_st_2005==0 & reserved_sc_2005==0 & reserved_w_2005==0 ~ "",
    reserved_bc_2005==0 & reserved_st_2005==1 & reserved_sc_2005==0 & reserved_w_2005==0 ~ "ST",
    reserved_bc_2005==0 & reserved_st_2005==0 & reserved_sc_2005==0 & reserved_w_2005==1 ~ "W",
    reserved_bc_2005==0 & reserved_st_2005==1 & reserved_sc_2005==0 & reserved_w_2005==1 ~ "ST + W")) %>%
  mutate(quota_2007 = case_when(
    reserved_bc_2007==0 & reserved_st_2007==0 & reserved_sc_2007==0 & reserved_w_2007==0 ~ "",
    reserved_bc_2007==0 & reserved_st_2007==1 & reserved_sc_2007==0 & reserved_w_2007==0 ~ "ST",
    reserved_bc_2007==0 & reserved_st_2007==0 & reserved_sc_2007==0 & reserved_w_2007==1 ~ "W",
    reserved_bc_2007==0 & reserved_st_2007==1 & reserved_sc_2007==0 & reserved_w_2007==1 ~ "ST + W")) %>%
  dplyr::select(district, taluk, gpidcode, scseats_theory, stseats_theory,
                bcseats_theory, quota_1993,
                quota_2000, quota_2002, quota_2005, quota_2007) %>%
  group_by(district, taluk) %>%
  arrange(district, taluk, desc(scseats_theory), desc(bcseats_theory),  desc(stseats_theory))
 
sc = x %>% dplyr::select(district, taluk, gpidcode, scseats_theory, 
                         quota_1993, quota_2000, quota_2002, quota_2005, quota_2007) %>%
  arrange(district, taluk, desc(scseats_theory), gpidcode)

sc2 = villages %>% dplyr::select(district, taluk, gpidcode, scseats_theory, 
                        reserved_sc_1993,  reserved_sc_2000, reserved_sc_2002, reserved_sc_2005, reserved_sc_2007) %>%
  arrange(district, taluk, desc(scseats_theory), gpidcode)

sc3 = x %>% dplyr::select(district, taluk, gpidcode, scseats_theory, 
                          quota_1993, quota_2000, quota_2002, quota_2005, quota_2007) %>%
  arrange(district, taluk, desc(scseats_theory), gpidcode)

st3 = x %>% dplyr::select(district, taluk, gpidcode, stseats_theory, 
                      quota_1993, quota_2000, quota_2002, quota_2005, quota_2007) %>%
  arrange(district, taluk, desc(stseats_theory), gpidcode)

st3 = st3 %>% filter(!is.na(stseats_theory))


final = st3 %>% filter(district==13 & taluk==2)

# create ordering of GPs for illustration
final$order <- c(1,2,5,7,3,4,6,8,9,10,13,11,12,14,15,16,17,18,19,20,21,23,24,22,25,27,26,28,29,30,31,32,33,34,35,36,37)
final <- final %>% arrange(order)
final <- final %>% dplyr::select(-order)
final <- final %>% mutate(gpidcode = str_remove(gpidcode, "13_2_")) %>% rename(GP_name = gpidcode)
final <- final %>% mutate(district = "Chitradurga", taluk = "Challakere")


write.csv(final, paste0(tab.out, "tableD4.csv"))
